﻿using System;
using System.Collections.Generic;

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using AMG.DALProfile;
using AMG.DALSQLServer;

namespace AMG.Web
{
    public partial class Custom : System.Web.UI.Page
    {
        public string R = "";
        public string staticcmpanyid;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindDropDownList(companyIDDropDownList, "SELECT [ID],[UserName] FROM [AMG_Proxy] where Isadmin=0 and IsUsed=1");
                BindDropDownList(ddlroundno, "SELECT [ID], [RoundName] FROM [AMG_Round]");
            }
            DbHelper.Provider = new DbBase();

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  AMG_UserInfo.ID, AMG_UserInfo.AMGCode, AMG_UserInfo.UserName, CASE Sex WHEN '0' THEN '男' ELSE '女' END AS sex,  ");
            strSql.Append("  AMG_UserInfo.Company, AMG_UserInfo.Positison, AMG_UserInfo.CRNo, ");
            strSql.Append("   CASE DriverLisecen WHEN '0' THEN '无' ELSE '有' END AS DriverLisecen, AMG_UserInfo.Phone, AMG_UserInfo.Address, AMG_UserInfo.Mail, AMG_UserInfo.ZipCode, ");
            strSql.Append("   CASE IsBook WHEN '0' THEN '否' ELSE '是' END AS isbook, AMG_UserInfo.CarNo, ");
            strSql.Append("  AMG_Round.RoundName  FROM AMG_UserInfo ");
            strSql.Append(" left join AMG_Proxy on AMG_UserInfo.CompanyID=AMG_Proxy.ID inner join AMG_Round on AMG_UserInfo.RoundNo=AMG_Round.ID");
            strSql.Append(" where (AMG_UserInfo.CompanyID=@CompanyID or @CompanyID is null) ");
            strSql.Append(" and (DriverLisecen=@DriverLisecen or @DriverLisecen is null) ");
            strSql.Append(" and (IsBook=@IsBook or @IsBook is null) ");
            strSql.Append(" and (AMG_Round.ID=@RoundNo or @RoundNo is null) ");
            SqlParameter[] parameters = {
                                        new SqlParameter("@CompanyID", SqlDbType.Int,4),
                                        new SqlParameter("@DriverLisecen", SqlDbType.Bit,1),
                                        new SqlParameter("@IsBook", SqlDbType.Bit,1),
                                        new SqlParameter("@RoundNo", SqlDbType.Int,4)
                                        };
            if (companyIDDropDownList.SelectedValue == "")
            {
                parameters[0].Value = DBNull.Value;
            }
            else
            {
                parameters[0].Value = companyIDDropDownList.SelectedValue;
            }

            if (driverLisecenDropDownList.SelectedValue == "")
            {
                parameters[1].Value = DBNull.Value;
            }
            else
            {
                parameters[1].Value = driverLisecenDropDownList.SelectedValue;
            }
            if (ddlisbook.SelectedValue == "")
            {
                parameters[2].Value = DBNull.Value;
            }
            else
            {
                parameters[2].Value = ddlisbook.SelectedValue;
            }
            if (ddlroundno.SelectedValue == "")
            {
                parameters[3].Value = DBNull.Value;
            }
            else
            {
                parameters[3].Value = ddlroundno.SelectedValue;
            }


            DataTable dt = DbHelper.ExecuteTable(CommandType.Text, strSql.ToString(), parameters);
            DataList1.DataSource = dt;
            DataList1.DataBind();
            R = DbHelper.ExecuteScalar(CommandType.Text, "select Count(*) from AMG_UserInfo where CustomerProperty=1 and CompanyID=@CompanyID", parameters[0]).ToString();
            Label1.Text = DbHelper.ExecuteScalar(CommandType.Text, "select sum(BookNo) as BookNo from AMG_Proxy where isadmin =0", null).ToString();
            Label2.Text = DbHelper.ExecuteScalar(CommandType.Text, "select Count(*) from AMG_UserInfo where CustomerProperty=1", null).ToString();
            DataBind();
        }

        protected void searchLinkButton_Click(object sender, EventArgs e)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  AMG_UserInfo.ID, AMG_UserInfo.AMGCode, AMG_UserInfo.UserName, CASE Sex WHEN '0' THEN '男' ELSE '女' END AS sex,  ");
            strSql.Append("  AMG_UserInfo.Company, AMG_UserInfo.Positison, AMG_UserInfo.CRNo, ");
            strSql.Append("   CASE DriverLisecen WHEN '0' THEN '无' ELSE '有' END AS DriverLisecen, AMG_UserInfo.Phone, AMG_UserInfo.Address, AMG_UserInfo.Mail, AMG_UserInfo.ZipCode, ");
            strSql.Append("   CASE IsBook WHEN '0' THEN '否' ELSE '是' END AS isbook, AMG_UserInfo.CarNo, ");
            strSql.Append("  AMG_Round.RoundName  FROM AMG_UserInfo ");
            strSql.Append(" left join AMG_Proxy on AMG_UserInfo.CompanyID=AMG_Proxy.ID inner join AMG_Round on AMG_UserInfo.RoundNo=AMG_Round.ID");
            strSql.Append(" where (AMG_UserInfo.CompanyID=@CompanyID or @CompanyID is null) ");
            strSql.Append(" and (DriverLisecen=@DriverLisecen or @DriverLisecen is null) ");
            strSql.Append(" and (IsBook=@IsBook or @IsBook is null) ");
            strSql.Append(" and (AMG_Round.ID=@RoundNo or @RoundNo is null) ");
            SqlParameter[] parameters = {
                                        new SqlParameter("@CompanyID", SqlDbType.Int,4),
                                        new SqlParameter("@DriverLisecen", SqlDbType.Bit,1),
                                        new SqlParameter("@IsBook", SqlDbType.Bit,1),
                                        new SqlParameter("@RoundNo", SqlDbType.Int,4)
                                        };
            if (companyIDDropDownList.SelectedValue == "")
            {
                parameters[0].Value = DBNull.Value;
            }
            else
            {
                parameters[0].Value = companyIDDropDownList.SelectedValue;
            }

            if (driverLisecenDropDownList.SelectedValue == "")
            {
                parameters[1].Value = DBNull.Value;
            }
            else
            {
                parameters[1].Value = driverLisecenDropDownList.SelectedValue;
            }
            if (ddlisbook.SelectedValue == "")
            {
                parameters[2].Value = DBNull.Value;
            }
            else
            {
                parameters[2].Value = ddlisbook.SelectedValue;
            }
            if (ddlroundno.SelectedValue == "")
            {
                parameters[3].Value = DBNull.Value;
            }
            else
            {
                parameters[3].Value = ddlroundno.SelectedValue;
            }

            DataTable dt = DbHelper.ExecuteTable(CommandType.Text, strSql.ToString(), parameters);
            R = dt.Rows.Count.ToString();
            DataBind();
        }

        protected void exportExcelLinkButton_Click(object sender, EventArgs e)
        {
            // data source
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  AMG_UserInfo.ID, AMG_UserInfo.AMGCode, AMG_UserInfo.UserName, CASE Sex WHEN '0' THEN '男' ELSE '女' END AS sex,  ");
            strSql.Append("  AMG_UserInfo.Company, AMG_UserInfo.Positison, ''''+AMG_UserInfo.CRNo as CRNo, ");
            strSql.Append("   CASE DriverLisecen WHEN '0' THEN '无' ELSE '有' END AS DriverLisecen, AMG_UserInfo.Phone, AMG_UserInfo.Address, AMG_UserInfo.Mail, AMG_UserInfo.ZipCode, ");
            strSql.Append("   CASE IsBook WHEN '0' THEN '否' ELSE '是' END AS isbook, AMG_UserInfo.CarNo, ");
            strSql.Append("  AMG_Round.RoundName  FROM AMG_UserInfo ");
            strSql.Append(" left join AMG_Proxy on AMG_UserInfo.CompanyID=AMG_Proxy.ID inner join AMG_Round on AMG_UserInfo.RoundNo=AMG_Round.ID");
            strSql.Append(" where (AMG_UserInfo.CompanyID=@CompanyID or @CompanyID is null) ");
            strSql.Append(" and (DriverLisecen=@DriverLisecen or @DriverLisecen is null) ");
            strSql.Append(" and (IsBook=@IsBook or @IsBook is null) ");
            strSql.Append(" and (AMG_Round.ID=@RoundNo or @RoundNo is null) ");
            SqlParameter[] parameters = {
                                        new SqlParameter("@CompanyID", SqlDbType.Int,4),
                                        new SqlParameter("@DriverLisecen", SqlDbType.Bit,1),
                                        new SqlParameter("@IsBook", SqlDbType.Bit,1),
                                        new SqlParameter("@RoundNo", SqlDbType.Int,4)
                                        };

            parameters[0].Value = DBNull.Value;
            parameters[1].Value = DBNull.Value;
            parameters[2].Value = DBNull.Value;
            parameters[3].Value = DBNull.Value;


            DataTable dt = DbHelper.ExecuteTable(CommandType.Text, strSql.ToString(), parameters);
            customExcelGridView.DataSource = dt;
            customExcelGridView.DataBind();

            // export to excel
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "GB2312";  //设置了类型为中文防止乱码的出现  
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + "1" + ".xls"); //定义输出文件和文件名
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
            Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
            this.EnableViewState = false;
            System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            this.customExcelGridView.RenderControl(oHtmlTextWriter);
            Response.Write(oStringWriter.ToString());
            Response.Flush();
            Response.End();
        }

        public override void VerifyRenderingInServerForm(Control control)
        {

        }

        protected void resetLinkButton_Click(object sender, EventArgs e)
        {
            companyIDDropDownList.SelectedIndex = 0;
            ddlisbook.SelectedIndex = 0;
            driverLisecenDropDownList.SelectedIndex = 0;
            ddlroundno.SelectedIndex = 0;
        }

        protected void companyIDDropDownList_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (companyIDDropDownList.SelectedValue != "")
            {
                Label1.Text = DbHelper.ExecuteScalar(CommandType.Text, "select sum(BookNo) as BookNo from AMG_Proxy where isadmin =0 and ID='" + companyIDDropDownList.SelectedValue + "'", null).ToString();
                Label2.Text = DbHelper.ExecuteScalar(CommandType.Text, "select Count(*) from AMG_UserInfo where CustomerProperty=1 and CompanyID='" + companyIDDropDownList.SelectedValue + "'", null).ToString();
            }
            else
            {
                Label1.Text = DbHelper.ExecuteScalar(CommandType.Text, "select sum(BookNo) as BookNo from AMG_Proxy where isadmin =0", null).ToString();
                Label2.Text = DbHelper.ExecuteScalar(CommandType.Text, "select Count(*) from AMG_UserInfo where CustomerProperty=1", null).ToString();
            }
        }

        public static void BindDropDownList(System.Web.UI.WebControls.DropDownList ddl, string sql)
        {
            if (sql == null || sql == "")
            {
                ddl.Items.Clear();
                return;
            }

            DataTable dt = DbHelper.ExecuteTable(CommandType.Text, sql, null);
            ddl.DataSource = dt;
            ddl.DataTextField = dt.Columns[1].ColumnName;
            ddl.DataValueField = dt.Columns[0].ColumnName;
            ddl.SelectedIndex = -1;
            ddl.SelectedValue = null;
            ddl.DataBind();
        }

        protected void companyIDDropDownList_DataBound(object sender, EventArgs e)
        {
            if (companyIDDropDownList.Items.Count <= 5)
            {
                ListItem newLI = new ListItem("所有代理商", "");
                companyIDDropDownList.Items.Insert(0, newLI);
            }
        }

        protected void ddlroundno_DataBound(object sender, EventArgs e)
        {
            if (ddlroundno.Items.Count <= 6)
            {
                ListItem newLI = new ListItem("参加的轮次", "");
                ddlroundno.Items.Insert(0, newLI);
            }
        }
    }
}
